﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using MySql.Data.MySqlClient;
using Minio;
using Minio.DataModel;
using Minio.Exceptions;
using static System.Windows.Forms.VisualStyles.VisualStyleElement.StartPanel;

namespace MyWebServer
{
    class MySQLManager
    {
        static string Connectstring = "server=127.0.0.1;port=3306;database=majiangdata;user=root;password=0605";
        static MySqlConnection connection = new MySqlConnection(Connectstring);
        public static bool IsUserExit(string name)
        {
            connection.Open();
            string CommandString = "SELECT 1 as isExist from majiangdata.user where username='"+name+"' limit 1;";
            MySqlCommand mySqlCommand = new MySqlCommand(CommandString, connection);
            MySqlDataAdapter adp = new MySqlDataAdapter(CommandString, connection);
            DataSet ds = new DataSet();
            adp.Fill(ds);
            connection.Close();
            if (ds.Tables[0].Rows.Count > 0)
                return true;
            else
                return false;
        }
        /// <summary>
        /// 使用ID去获取单个用户名称的方法
        /// </summary>
        /// <param name="id"></param>
        public static string UseIDfindusername(int id)
        {
            connection.Open();
            string CommandString = "select username from majiangdata.user where id="+id+";";
            MySqlCommand mySqlCommand = new MySqlCommand(CommandString,connection);
            string username=(string)mySqlCommand.ExecuteScalar();
            Console.WriteLine(username);
            connection.Close();
            return username;
        }
        /// <summary>
        /// 使用ID去获取单个金币数量的方法
        /// </summary>
        /// <param name="id"></param>
        public static int UseIDfindCoin(int id)
        {
            connection.Open();
            string CommandString = "select coin from majiangdata.user where id=" + id + ";";
            MySqlCommand mySqlCommand = new MySqlCommand(CommandString, connection);
            int CoinNumber = (int)mySqlCommand.ExecuteScalar();
            Console.WriteLine(CoinNumber);
            connection.Close();
            return CoinNumber;
        }
        /// <summary>
        /// 使用ID去获取钻石数量的方法
        /// </summary>
        /// <param name="id"></param>
        public static int UseIDfindDiamond(int id)
        {
            connection.Open();
            string CommandString = "select diamond from majiangdata.user where id=" + id + ";";
            MySqlCommand mySqlCommand = new MySqlCommand(CommandString, connection);
            int DiamondNumber = (int)mySqlCommand.ExecuteScalar();
            Console.WriteLine(DiamondNumber);
            connection.Close();
            return DiamondNumber;
        }
        /// <summary>
        /// 使用ID去获取用户性别的方法
        /// </summary>
        /// <param name="id"></param>
        public static int UseIDfindsex(int id)
        {
            connection.Open();
            string CommandString = "select sex from majiangdata.user where id=" + id + ";";
            MySqlCommand mySqlCommand = new MySqlCommand(CommandString, connection);
            int sex = (int)mySqlCommand.ExecuteScalar();
            Console.WriteLine(sex);
            connection.Close();
            return sex;
        }
        /// <summary>
        /// 根据ID查找用户是否领过奖
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        /// 
        public static string UseUserNamefindPassword(string username)
        {
            connection.Open();
            string CommandString = "select password from majiangdata.user where username='" + username + "';";
            MySqlCommand mySqlCommand = new MySqlCommand(CommandString, connection);
            string password = (string)mySqlCommand.ExecuteScalar();
            Console.WriteLine(password);
            connection.Close();
            return password;
        }
        public static int UseUserNamefindid(string username)
        {
            connection.Open();
            string CommandString = "select id from majiangdata.user where username=" + "'"+username+"'" + ";";
            MySqlCommand mySqlCommand = new MySqlCommand(CommandString, connection);
            int id = (int)mySqlCommand.ExecuteScalar();
            Console.WriteLine(id);
            connection.Close();
            return id;
        }
        public static bool UseIDfindLingJingMessage(int id)
        {
            connection.Open();
            string CommandString = "select islingjiang from majiangdata.user where id=" + id + ";";
            MySqlCommand mySqlCommand = new MySqlCommand(CommandString, connection);
            int islingjiang = (int)mySqlCommand.ExecuteScalar();
            Console.WriteLine(islingjiang);
            connection.Close();
            if (islingjiang == 0)
            {
                return false;
            }
            else 
            {
                return true;
            }
            
        }
        public static int UseIDfindJinRiCoinJuShuMessage(int id)
        {
            connection.Open();
            string CommandString = "select jinripipeijushu from majiangdata.user where id=" + id + ";";
            MySqlCommand mySqlCommand = new MySqlCommand(CommandString, connection);
            int jinripipeijushu = (int)mySqlCommand.ExecuteScalar();
            Console.WriteLine(jinripipeijushu);
            connection.Close();
            return jinripipeijushu;

        }
        public static int UseIDfindJinRiYvePaiJuShuMessage(int id)
        {
            connection.Open();
            string CommandString = "select jinriyvepaijushu from majiangdata.user where id=" + id + ";";
            MySqlCommand mySqlCommand = new MySqlCommand(CommandString, connection);
            int jinriyvepaijushu = (int)mySqlCommand.ExecuteScalar();
            Console.WriteLine(jinriyvepaijushu);
            connection.Close();
            return jinriyvepaijushu;

        }
        public static int UseIDfindJinRiZongJuShuMessage(int id)
        {
            connection.Open();
            string CommandString = "select jinrizongjushu from majiangdata.user where id=" + id + ";";
            MySqlCommand mySqlCommand = new MySqlCommand(CommandString, connection);
            int jinrizongjushu = (int)mySqlCommand.ExecuteScalar();
            Console.WriteLine(jinrizongjushu);
            connection.Close();
            return jinrizongjushu;
        }
        public static int UseIDfindZongJuShuMessage(int id)
        {
            connection.Open();
            string CommandString = "select zongjushu from majiangdata.user where id=" + id + ";";
            MySqlCommand mySqlCommand = new MySqlCommand(CommandString, connection);
            int zongjushu = (int)mySqlCommand.ExecuteScalar();
            Console.WriteLine(zongjushu);
            connection.Close();
            return zongjushu;
        }
        public static bool UseIDfindCanLingQvRenWu1(int id)
        {
            connection.Open();
            string CommandString = "select canlingqvrenwu1 from majiangdata.user where id=" + id + ";";
            MySqlCommand mySqlCommand = new MySqlCommand(CommandString, connection);
            int canlingqvrenwu1 = (int)mySqlCommand.ExecuteScalar();
            Console.WriteLine(canlingqvrenwu1);
            connection.Close();
            if (canlingqvrenwu1 == 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        public static bool UseIDfindCanLingQvRenWu2(int id)
        {
            connection.Open();
            string CommandString = "select canlingqvrenwu2 from majiangdata.user where id=" + id + ";";
            MySqlCommand mySqlCommand = new MySqlCommand(CommandString, connection);
            int canlingqvrenwu2 = (int)mySqlCommand.ExecuteScalar();
            Console.WriteLine(canlingqvrenwu2);
            connection.Close();
            if (canlingqvrenwu2 == 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        public static bool UseIDfindCanLingQvRenWu3(int id)
        {
            connection.Open();
            string CommandString = "select canlingqvrenwu3 from majiangdata.user where id=" + id + ";";
            MySqlCommand mySqlCommand = new MySqlCommand(CommandString, connection);
            int canlingqvrenwu3 = (int)mySqlCommand.ExecuteScalar();
            Console.WriteLine(canlingqvrenwu3);
            connection.Close();
            if (canlingqvrenwu3 == 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        public static bool UseIDfindIsMusic(int id)
        {
            if (connection.State == ConnectionState.Open)
            {

            }
            else if (connection.State == ConnectionState.Closed)
            {
                connection.Open();
            }
            string CommandString = "select ismusic from majiangdata.user where id=" + id + ";";
            MySqlCommand mySqlCommand = new MySqlCommand(CommandString, connection);
            int ismusic = (int)mySqlCommand.ExecuteScalar();
            Console.WriteLine(ismusic);
            connection.Close();
            if (ismusic == 0)
            {
                return false;
            }
            else
            {
                return true;
            }
        }
        public static string UseIDfindBanBenHao(int id)
        {
            if (connection.State == ConnectionState.Open)
            {

            }
            else if (connection.State == ConnectionState.Closed)
            {
                connection.Open();
            }
            string CommandString = "select banben from majiangdata.user where id=" + id + ";";
            MySqlCommand mySqlCommand = new MySqlCommand(CommandString, connection);
            string benbenhao = (string)mySqlCommand.ExecuteScalar();
            connection.Close();
            return benbenhao;
        }
        public static bool UseIDfinIsNeedReDownLoad(int id)
        {
            if (connection.State == ConnectionState.Open)
            {

            }
            else if (connection.State == ConnectionState.Closed)
            {
                connection.Open();
            }
            string CommandString = "select redownload from majiangdata.user where id=" + id + ";";
            MySqlCommand mySqlCommand = new MySqlCommand(CommandString, connection);
            int IsRedownLoad = (int)mySqlCommand.ExecuteScalar();
            connection.Close();
            if (IsRedownLoad == 0)
            {
                return false;
            }
            else
            {
                return true;
            }
        }
        public static List<PaiHangData> QueryPaiHang(int num)
        {
            if (connection.State == ConnectionState.Open)
            {

            }
            else if(connection.State == ConnectionState.Closed)
            {
                connection.Open();
            }
            
            Console.WriteLine("连接成功");
            List<PaiHangData> list = new List<PaiHangData>();
            string commandStr = "select username,coin,diamond,sex from user order by coin desc limit " + num + "";
            MySqlCommand mySqlCommand = new MySqlCommand(commandStr, connection);
            MySqlDataReader reader = mySqlCommand.ExecuteReader();
            bool a = false;
            for (int i = 0; i < num; i++)
            {
                a = reader.Read();
                if (a)
                {
                    PaiHangData paiHang = new PaiHangData();
                    paiHang.username = reader.GetString(0);
                    paiHang.coin = reader.GetInt32(1);
                    paiHang.diamond = reader.GetInt32(2);
                    paiHang.sex = reader.GetInt32(3);
                    list.Add(paiHang);
                }
            }
            connection.Close();
            connection.Dispose();
            return list;
        }
        public static int QueryMyMingCi(int id)
        {
            connection.Open();
            int mingci = 0;
            string commandStr = "SELECT count(1) AS rank_num FROM user WHERE coin >= (SELECT coin FROM user where id = " + id + " )";
            MySqlCommand mySqlCommand = new MySqlCommand(commandStr, connection);

            MySqlDataReader reader = mySqlCommand.ExecuteReader();
            bool b = reader.Read();
            if (b)
            {
                mingci = reader.GetInt32(0);
            }
            connection.Close();
            connection.Dispose();
            return mingci;
        }
        /// <summary>
        /// 更新一下领奖信息
        /// </summary>
        /// <param name="changecoin"></param>
        /// <param name="userid"></param>
        /// <returns></returns>
        public static int UpdateIslingjiang(int change, int userid)
        {
            MySqlConnection msc = new MySqlConnection(Connectstring);
            int i = 0;
            try
            {
                msc.Open();

                string commandStr = "update user set islingjiang = islingjiang+" + change + " where id = " + userid + "";
                MySqlCommand mySqlCommand = new MySqlCommand(commandStr, msc);

                i = mySqlCommand.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
            }

            finally
            {
                msc.Close();
                msc.Dispose();
            }
            return i;

        }
        public static int UpdateCoin(int changecoin, int userid)
        {
            MySqlConnection msc = new MySqlConnection(Connectstring);
            int i = 0;
            try
            {
                msc.Open();

                string commandStr = "update user set coin = coin+" + changecoin + " where id = " + userid + "";
                MySqlCommand mySqlCommand = new MySqlCommand(commandStr, msc);

                i = mySqlCommand.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
            }

            finally
            {
                msc.Close();
                msc.Dispose();
            }
            return i;

        }

        public static int UpdateDiamond(int changediamond, int userid)
        {
            MySqlConnection msc = new MySqlConnection(Connectstring);
            int i = 0;
            try
            {
                msc.Open();

                string commandStr = "update user set diamond = diamond+" + changediamond + " where id = " + userid + "";
                MySqlCommand mySqlCommand = new MySqlCommand(commandStr, msc);

                i = mySqlCommand.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
            }

            finally
            {
                msc.Close();
                msc.Dispose();
            }
            return i;

        }

        public static int UpdateCanLingQuRenwu1(int changecanlingqvrenwu1, int userid)
        {
            MySqlConnection msc = new MySqlConnection(Connectstring);
            int i = 0;
            try
            {
                msc.Open();

                string commandStr = "update user set canlingqvrenwu1 =" + changecanlingqvrenwu1 + " where id = " + userid + "";
                MySqlCommand mySqlCommand = new MySqlCommand(commandStr, msc);

                i = mySqlCommand.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
            }

            finally
            {
                msc.Close();
                msc.Dispose();
            }
            return i;

        }
        public static int UpdateCanLingQuRenwu2(int changecanlingqvrenwu2, int userid)
        {
            MySqlConnection msc = new MySqlConnection(Connectstring);
            int i = 0;
            try
            {
                msc.Open();

                string commandStr = "update user set canlingqvrenwu2 =" + changecanlingqvrenwu2 + " where id = " + userid + "";
                MySqlCommand mySqlCommand = new MySqlCommand(commandStr, msc);

                i = mySqlCommand.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
            }

            finally
            {
                msc.Close();
                msc.Dispose();
            }
            return i;

        }
        public static int UpdateCanLingQuRenwu3(int changecanlingqvrenwu3, int userid)
        {
            MySqlConnection msc = new MySqlConnection(Connectstring);
            int i = 0;
            try
            {
                msc.Open();

                string commandStr = "update user set canlingqvrenwu3 =" + changecanlingqvrenwu3 + " where id = " + userid + "";
                MySqlCommand mySqlCommand = new MySqlCommand(commandStr, msc);

                i = mySqlCommand.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
            }

            finally
            {
                msc.Close();
                msc.Dispose();
            }
            return i;

        }
        public static int UpdatejinriCoinJushu(int changeJushu, int userid)
        {
            MySqlConnection msc = new MySqlConnection(Connectstring);
            int i = 0;
            try
            {
                msc.Open();

                string commandStr = "update user set jinripipeijushu =jinripipeijushu+" + changeJushu + " where id = " + userid + "";
                MySqlCommand mySqlCommand = new MySqlCommand(commandStr, msc);

                i = mySqlCommand.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
            }

            finally
            {
                msc.Close();
                msc.Dispose();
            }
            return i;

        }
        public static int UpdatejinriZongJushu(int changeJushu, int userid)
        {
            MySqlConnection msc = new MySqlConnection(Connectstring);
            int i = 0;
            try
            {
                msc.Open();

                string commandStr = "update user set jinrizongjushu =jinrizongjushu+" + changeJushu + " where id = " + userid + "";
                MySqlCommand mySqlCommand = new MySqlCommand(commandStr, msc);

                i = mySqlCommand.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
            }

            finally
            {
                msc.Close();
                msc.Dispose();
            }
            return i;

        }
        public static int UpdateIsMusic(int change, int userid)
        {
            MySqlConnection msc = new MySqlConnection(Connectstring);
            int i = 0;
            try
            {
                msc.Open();

                string commandStr = "update user set ismusic =" + change + " where id = " + userid + "";
                MySqlCommand mySqlCommand = new MySqlCommand(commandStr, msc);

                i = mySqlCommand.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
            }

            finally
            {
                msc.Close();
                msc.Dispose();
            }
            return i;

        }
        public static int GetMaxid()
        {
            connection.Open();
            string CommandString = "select max(id) from majiangdata.user;";
            MySqlCommand mySqlCommand = new MySqlCommand(CommandString, connection);
            int id = (int)mySqlCommand.ExecuteScalar();
            Console.WriteLine(id);
            connection.Close();
            return id;
        }
        public static void InsertNewData(int id,string username, string password)
        {
            connection.Open();
            string CommandString = "INSERT INTO majiangdata.user SET id='"+id+"',username='"+username+"',password='"+password+"';";
            MySqlCommand mySqlCommand = new MySqlCommand(CommandString, connection);
            mySqlCommand.ExecuteNonQuery();
            connection.Close();

        }

    }
}
